package dao.impl;

import dao.RoleDao;
import model.Menu;
import model.Role;
import model.User;
import util.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl extends DBUtils implements RoleDao {

    @Override
    public List<Role> getRole() {
        String sql = "select * from role";
        List<Role> roleList = new ArrayList<>();
        Role role = null;
        resultSet = query(sql);
        try {
            while (resultSet.next()) {
                role = new Role();
                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));
                roleList.add(role);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return roleList;
    }

    @Override
    public List<Role> getAllRole(int pageIndex, int pageSize) {
        String sql = "select * from role limit ?,?";
        List<Role> roleList = new ArrayList<>();
        Role role = null;
        resultSet = query(sql, (pageIndex - 1) * pageSize, pageSize);
        try {
            while (resultSet.next()) {
                role = new Role();
                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));
                roleList.add(role);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return roleList;
    }

    @Override
    public int total() {
        String sql = "select COUNT(1) from role ";
        int total = 0;
        resultSet = query(sql);
        try {
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int insert(Role role) {
        int key = 0;//新增数据的id值
        try {
            String sql = "insert into role(rolename,rolestate) values(?,?)";
            int update = update(sql, role.getRoleName(), role.getRoleState());
            ResultSet generatedKeys = pps.getGeneratedKeys();
            if (generatedKeys.next()) {
                key = generatedKeys.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return key;
    }

    @Override
    public Role getRoleById(int id) {
        List<Menu> menuList = new ArrayList<>();
        Role role = new Role();
        String sql = "select r.roleid,r.rolename,r.rolestate,m.* from role r,middle,menu m\n" +
                "where r.roleid=middle.roleid\n" +
                "and middle.menuid=m.menuid\n" +
                "and r.roleid=?";
        resultSet = query(sql, id);
        try {
            while (resultSet.next()) {
                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));
                Menu menu = new Menu();
                menu.setMenuId(resultSet.getInt("menuid"));
                menu.setMuneName(resultSet.getString("menuname"));
                menu.setState(resultSet.getInt("state"));
                menu.setUrl(resultSet.getString("url"));
                menu.setInfo(resultSet.getString("info"));
                menu.setUpmenuId(resultSet.getInt("upmenuid"));
                menuList.add(menu);
            }
            role.setMenus(menuList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return role;
    }

    @Override
    public int update(Role role) {
        String sql = "update role set rolename=?,rolestate=? where roleid=?";
        int update = 0;
        try {
            update = update(sql, role.getRoleName(), role.getRoleState(), role.getRoleId());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delete(int roleid) {
        String sql = "delete from role where roleid = ?";
        int update = 0;
        try {
            update = update(sql, roleid);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public Role findById(int roleid) {
        List<Menu> menuList = new ArrayList<>();
        Role role = new Role();
        String sql = "select * from role r,middle,menu m\n" +
                "where r.roleid=middle.roleid\n" +
                "  and middle.menuid=m.menuid\n" +
                "  and r.roleid=?";
        resultSet = query(sql, roleid);
        try {
            while (resultSet.next()) {
                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));
                Menu menu = new Menu();
                menu.setMenuId(resultSet.getInt("menuid"));
                menu.setMuneName(resultSet.getString("menuname"));
                menu.setState(resultSet.getInt("state"));
                menu.setUrl(resultSet.getString("url"));
                menu.setInfo(resultSet.getString("info"));
                menu.setUpmenuId(resultSet.getInt("upmenuid"));
                menuList.add(menu);
            }
            role.setMenus(menuList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return role;
    }

}
